﻿using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Data.SqlClient;
using System.Web.Configuration;

namespace Perm.Web
{
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class getCountVote
    {
        string select = "",
               String = "",
               error = null;
        int voteCount = 0;

        SqlConnection conMSSQL;
        SqlDataReader readerMSSQL;
        SqlCommand commandMSSQL;

        DateTime thisDate = DateTime.Now.ToUniversalTime().AddHours(6);

        [OperationContract]
        public int DoWork(string city)
        {
            try
            {
                string connectionStringMSSQL = WebConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString;
                try
                {
                    conMSSQL = new SqlConnection(connectionStringMSSQL);
                    conMSSQL.Open();
                }
                catch { error += " Невозможно подключиться к базе данных. MS SQL (getCountVote)"; }

                switch (city)
                {
                    case "perm":
                        {
                            select = "SELECT TOP 1 ((SELECT Sum(VOTE) as 'VOTE_GAS' FROM RATING_GAS_PERM) + (SELECT Sum(VOTE) as 'VOTE_ROAD' FROM RATING_ROAD_PERM) + (SELECT Sum(VOTE) as 'VOTE_CARWASH' FROM RATING_CARWASH_PERM)) AS VOTES FROM RATING_GAS_PERM, RATING_ROAD_PERM, RATING_CARWASH_PERM";
                            break;
                        }
                    case "ekb":
                        {
                            select = "SELECT TOP 1 ((SELECT Sum(VOTE) as 'VOTE_GAS' FROM RATING_GAS_EKB) + (SELECT Sum(VOTE) as 'VOTE_ROAD' FROM RATING_ROAD_EKB) + (SELECT Sum(VOTE) as 'VOTE_CARWASH' FROM RATING_CARWASH_EKB)) AS VOTES FROM RATING_GAS_EKB, RATING_ROAD_EKB, RATING_CARWASH_EKB";
                            break;
                        }
                    case "permkray":
                        {
                            select = "SELECT TOP 1 ((SELECT Sum(VOTE) as 'VOTE_GAS' FROM RATING_GAS_PERMKRAY) + (SELECT Sum(VOTE) as 'VOTE_ROAD' FROM RATING_ROAD_PERMKRAY) + (SELECT Sum(VOTE) as 'VOTE_CARWASH' FROM RATING_CARWASH_PERMKRAY)) AS VOTES FROM RATING_GAS_PERMKRAY, RATING_ROAD_PERMKRAY, RATING_CARWASH_PERMKRAY";
                            break;
                        }
                }  

                try
                {
                    commandMSSQL = new SqlCommand(select, conMSSQL);
                    readerMSSQL = commandMSSQL.ExecuteReader();
                    readerMSSQL.Read();

                    voteCount = (readerMSSQL[0].ToString() != "") ? (int)readerMSSQL.GetDouble(0) : 0;
                }
                catch { error += " Невозможно получить сумму голосов."; }

                if (error != null)
                {
                    switch (city)
                    {
                        case "perm":
                            {
                                String = "INSERT INTO ERRORS (DATA, ERROR) VALUES ('" + thisDate + "', '" + error + "')";
                                break;
                            }
                        case "ekb":
                            {
                                String = "INSERT INTO ERRORS_EKB (DATA, ERROR) VALUES ('" + thisDate + "', '" + error + "')";
                                break;
                            }
                        case "permkray":
                            {
                                String = "INSERT INTO ERRORS_PERMKRAY (DATA, ERROR) VALUES ('" + thisDate + "', '" + error + "')";
                                break;
                            }
                    }
                    try
                    {
                        commandMSSQL = new SqlCommand(String, conMSSQL);
                        commandMSSQL.ExecuteNonQuery();
                    }
                    catch { }
                }
                conMSSQL.Close();
            }
            catch { }
            return voteCount;
        }

        // Add more operations here and mark them with [OperationContract]
    }
}
